Skip Headers

Oracle® interMedia User's Guide 10g Release 2 (10.2) Part Number B14302-01 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# 9 Oracle interMedia Examples

This chapter provides examples that show common operations with Oracle interMedia ("interMedia"). The examples are presented by the following data groups:

- Audio (Section 9.1)

- Media (Section 9.2)

- Image (Section 9.3)

- Video (Section 9.4)

These sections include scripts that perform common operations, including these:

- Populating an ORDAudio object with BLOB data (Section 9.1.2)

- Populating an ORDDoc object from a file data source (Section 9.2.1)

- Populating an ORDImage object from a file data source (Section 9.3.1)

- Loading an image table from an HTTP data source (Section 9.3.2)

- Addressing globalization support issues (Section 9.3.3)

These scripts, and other examples, can be found on the Oracle interMedia Sample Code section of the Oracle Technology Network (OTN) Web site at

`http://www.oracle.com/technology/products/intermedia/index.html`

Select Sample Code under Oracle inter Media Resources to go to the Oracle interMedia Sample Code Web page. On that page, select Use inter Media and PL/SQL to manage media content under inter Media Code Samples.

## 9.1 Audio Data Examples

Audio data examples using interMedia include the following common operations:

- Using interMedia with object views (See Section 9.1.1)

- Using a set of scripts for populating an ORDAudio object with BLOB data stored in the database (See Section 9.1.2)

Reference information on the methods used in these examples is presented in Oracle interMedia Reference.

### 9.1.1 Using Audio Types with Object Views

This section describes how to use audio types with object views. Just as a view is a virtual table, an object view is a virtual object table.

Oracle provides object views as an extension of the basic relational view mechanism. By using object views, you can create virtual object tables -- of either built-in or user-defined types -- from data stored in the columns of relational or object tables in the database.

Object views can offer specialized or restricted access to the data and objects in a database. For example, you might use an object view to provide a version of an employee object table that does not have attributes containing sensitive data or a deletion method. Object views also let you try object-oriented programming without permanently converting your tables. Using object views, you can convert data gradually and transparently from relational tables to object-relational tables.

In Example 9-1, consider the following relational table (containing no ORDAudio objects).

Example 9-1 Define a Relational Table Containing No ORDAudio Object

    create table flat (
       id                NUMBER,
       description       VARCHAR2(4000),
       localData         BLOB,
       srcType           VARCHAR2(4000),
       srcLocation       VARCHAR2(4000),
       srcName           VARCHAR2(4000),
       upDateTime        DATE,
       local             NUMBER,
       format            VARCHAR2(31),
       mimeType          VARCHAR2(4000),
       comments          CLOB,
       encoding          VARCHAR2(256),
       numberOfChannels  NUMBER,
       samplingRate      NUMBER,
       sampleSize        NUMBER,
       compressionType   VARCHAR2(4000),
       audioDuration     NUMBER,
    );

You can create an object view on the relational table shown in Example 9-1 as follows in Example 9-2.

Example 9-2 Define an Object View Containing an ORDAudio Object and Relational Columns

    create or replace view object_audio_v as
      select
          id,
          ORDSYS.ORDAudio(T.description,
          ORDSYS.ORDSource(
             T.localData, T.srctype, T.srcLocation, T.srcName, T.updateTime,
             T.local),
             T.format,
             T.mimeType,
             T.comments,
             T.encoding,
             T.numberOfChannels,
             T.samplingRate,
             T.sampleSize,
             T.compressionType,
             T.audioDuration)
       from flat T;

Object views provide the flexibility of looking at the same relational or object data in more than one way. Therefore, you can use different in-memory object representations for different applications without changing the way you store the data in the database. See Oracle Database Concepts for more information about defining, using, and updating object views.

### 9.1.2 Scripts for Populating an ORDAudio Object with BLOB Data

The scripts presented in this section demonstrate how to populate an interMedia ORDAudio object from an existing BLOB stored in the database.

Table 9-1 lists each script by name, along with a brief description of the operations it performs. Each script is included and described in further detail in the sections that follow.

Table 9-1 Audio Scripts

Script Name | Operations Performed
--- | ---
`create_mediadir.sql` Example 9-3 | Creates an audio data load directory. (See Section 9.1.2.1)
`create_soundtable.sql` Example 9-4 | Creates and populates the `soundtable` table. (See Section 9.1.2.2)
`create_audtable.sql` Example 9-5 | Creates and populates the `audio_table` table. (See Section 9.1.2.3)
`import_aud.sql` Example 9-6 | Loads the audio data. This script imports the audio data from the `soundtable` table into the `audio_table` table using the ORDAudio import( ) method. (See Section 9.1.2.4)
`copy_audblob.sql` Example 9-7 | Copies the BLOB data from the `soundtable` table to the `audio_table` table using a SQL UPDATE statement. (See Section 9.1.2.5)
`showprop_aud.sql` Example 9-8 | Displays the properties of the loaded audio data stored in the `audio_table` table. (See Section 9.1.2.6)
`setup_audsample.sql` Example 9-9 | Automates the process by running the previous audio scripts in the required order. (See Section 9.1.2.7)
`cleanup_audsample.sql` Example 9-10 | Cleans up the process by removing the sample tables, directories, and procedures from your database. (See Section 9.1.2.8)

#### 9.1.2.1 Create an Audio Data Load Directory

The `create_mediadir.sql` script creates the audio data load directory. This script is shown in Example 9-3.

To successfully load the audio data, you must have a `mediadir` directory created on your system. This directory must contain your sample audio clip file, `aud1.wav` , which is installed in the `<ORACLE_HOME>``/ord/aud/demo` directory. Actually, you can copy any supported audio data clip file to the `mediadir` directory to run this script. Be sure to change the data file name in the script to correspond with the name of the data file you use. This directory path and disk drive must be specified in the CREATE DIRECTORY statement in the `create_mediadir.sql` file.

Note: You must have the CREATE ANY DIRECTORY privilege to run this script. If you wish to delete previous instances of the audio data load directory, you will also need the DROP ANY DIRECTORY privilege. You must edit the `create_mediadir.sql` file to replace the directory path in the CREATE DIRECTORY statement. If you run the `create_mediadir.sql` script under a different user than you run the other demo scripts, you must also replace the user in the GRANT READ statement. See the following script for detailed instructions.

Example 9-3 create_mediadir.sql

    -- create_mediadir.sql
    -- You must have the CREATE ANY DIRECTORY privilege to run this script.
    --
    -- You must edit this script to:
    --  o replace the directory path in the CREATE DIRECTORY statement
    --    with your own (see below for details)
    --  o uncomment the GRANT READ ON DIRECTORY statement and grant to the
    --    user under which you will run this demo

    SET SERVEROUTPUT ON;
    SET ECHO ON;

    -- You need DROP ANY DIRECTORY privilege to delete a directory. If there
    -- is no need to delete the directory, then leave the next line
    -- commented out.
    -- DROP DIRECTORY mediadir;

    -- Create the mediadir load directory, the directory where the image
    -- clips reside. You must specify the disk drive and path in the
    -- CREATE DIRECTORY statement below. Edit this file to replace the
    -- directory specification with your own.
    CREATE OR REPLACE DIRECTORY mediadir AS 'C:/mediadir';

    -- If you intend to run this demo under a user other than the user
    -- that just created this directory, edit the following grant to grant
    -- READ on the directory just created to the user under which you will
    -- run the demo scripts.  For example, if you will run the demo scripts
    -- under user 'SCOTT', then replace the string "<USER>" with "SCOTT".
    -- Then uncomment the following GRANT statement.  There is no need to
    -- do this if the user creating the directory will also be used to run
    -- the other demo scripts.
    -- GRANT READ ON DIRECTORY mediadir TO <USER>;

#### 9.1.2.2 Create and Populate the soundtable Table

The `create_soundtable.sql` script creates and populates the `soundtable` table. This table contains a BLOB column; it shows how to populate a table with an interMedia ORDAudio column from a table with a BLOB column. The `soundtable` table is created for demonstration purposes only. This script is shown in Example 9-4.

To demonstrate how to populate a table with an interMedia ORDAudio column from a table with a BLOB column, first we must have a table with a BLOB column. The `soundtable` table is our sample table with a BLOB column. This script creates the soundtable table, inserts a row with an empty BLOB, loads the BLOB with with audio data, and then checks the length of the BLOB data.

Note: This script must be run from a user with the CREATE TABLE privilege.

Example 9-4 create_soundtable.sql

    -- create_soundtable.sql
    --
    -- This script must be run from a user with CREATE TABLE privilege.
    --
    -- Create the soundtable table.  This table is used ONLY to show
    -- how to copy data from a BLOB column to an ORDAudio column.
    --
    -- Insert a row into the table with an empty BLOB.
    -- Load the row with BLOB data by pointing to the audio file to
    -- be loaded from the directory specified using the BFILE data
    -- type.
    -- Close the files and commit the transaction.
    -- Check the length of the BLOB loaded. Is the length
    -- what you are expecting?
    --

    SET SERVEROUTPUT ON;
    SET ECHO ON;

    DROP TABLE soundtable PURGE;
    CREATE TABLE soundtable (id number,
                             sound BLOB
           default EMPTY_BLOB());

    INSERT INTO soundtable(id, sound) VALUES (1, EMPTY_BLOB());
    COMMIT;

    DECLARE
       f_lob BFILE := BFILENAME('MEDIADIR','aud1.wav');
       b_lob BLOB;
       Lob BLOB;
       Length INTEGER;
    BEGIN

      SELECT sound INTO b_lob FROM soundtable WHERE id=1 FOR UPDATE;

    -- Open the LOBs.
      dbms_lob.open(f_lob, dbms_lob.file_readonly);
      dbms_lob.open(b_lob, dbms_lob.lob_readwrite);

    -- Populate the BLOB from the 'aud1.wav' file in the BFILE
      dbms_lob.loadfromfile
         (b_lob, f_lob, dbms_lob.getlength(f_lob));

    -- Close the LOBs.
      dbms_lob.close(b_lob);
      dbms_lob.close(f_lob);
      COMMIT;

    -- Select the LOB:
      SELECT sound INTO Lob FROM soundtable
          WHERE ID = 1;

    -- Opening the LOB is optional.
      DBMS_LOB.OPEN (Lob, DBMS_LOB.LOB_READONLY);
    -- Get the length of the LOB and verify length is not null.
      length := DBMS_LOB.GETLENGTH(Lob);
      IF length IS NULL THEN
        DBMS_OUTPUT.PUT_LINE('LOB is null.');
      ELSE
        DBMS_OUTPUT.PUT_LINE('The length is ' || length);
      END IF;
    -- Closing the LOB is mandatory if you have opened it.
      DBMS_LOB.CLOSE (Lob);
    END;
    /

#### 9.1.2.3 Create and Populate the audio_table Table

The `create_audtable.sql` script creates and populates the `audio_table` table. This table contains an ORDAudio column. This script creates the `audio_table` table with two columns (`id`, `audio`), inserts two rows, and initializes the `audio` column. This script is shown in Example 9-5.

Note: Calling the ORDAudio.init( ) method initializes the blob attribute of the ORDAudio object. This is necessary to populate the BLOB with data. This script must be run from a user with the CREATE TABLE privilege.

Example 9-5 create_audtable.sql

    -- create_audtable.sql
    --
    -- This script must be run from a user with the CREATE TABLE privilege.
    --
    -- Create the audio_table table.
    -- Insert two rows with empty BLOBs and initialize object attributes.

    SET SERVEROUTPUT ON;
    SET ECHO ON;

    DROP TABLE audio_table PURGE;

    CREATE TABLE audio_table (id NUMBER,
                           audio ORDAudio)
    LOB(audio.source.localData) --store audio with 32k chunk
    store as (chunk 32K);

    -- Insert rows with an empty BLOB and initialize the object attributes.

    INSERT INTO audio_table VALUES(1,ORDAudio.init());
    INSERT INTO audio_table VALUES(2,ORDAudio.init());

    COMMIT;

#### 9.1.2.4 Load the Audio Data

The `import_aud.sql` script imports audio data from an audio file (in the `soundtable` table) into the ORDAudio column in the `audio_table` table using the ORDAudio import( ) method. To import data into a blob attribute where the audio clip will be stored, the audio column in the row must be selected `for update`. This script is shown in Example 9-6.

To successfully run this script, you must copy one audio clip to your `mediadir` directory using the names specified in this script, or modify this script to match the file names of your audio clips.

This script should load the same audio clip that was loaded by the `create_soundtable.sql` script. It is used later in the `showprop_aud.sql` script to show that data loaded with the import( ) method matches the data copied from the BLOB column of the `soundtable` table.

Note: This script should be run from the same user as the scripts inExample 9-4 and Example 9-5.

Example 9-6 import_aud.sql

    --import_aud.sql

    DECLARE
      obj ORDAUDIO;
      ctx RAW(64) := NULL;

    BEGIN

      -- selects the audio column for update
      SELECT audio INTO obj FROM audio_table WHERE id = 1 FOR UPDATE;

      --import audio clip aud1.wav from mediadir
      obj.setSource('FILE','MEDIADIR','aud1.wav');
      obj.import(ctx);

      --set properties
      obj.setProperties(ctx);

      --update table with audio object
      UPDATE audio_table SET audio = obj WHERE id = 1;

      COMMIT;

    END;
    /

#### 9.1.2.5 Copy the BLOB Data to the ORDAudio Object

The `copy_audblob.sql` script copies the audio data in the `sound` column of the `soundtable` table into the ORDAudio object column of the `audio_table` table for a row with `id=2`. The script uses a SQL UPDATE statement to copy the BLOB data from the `soundtable` table into the `T.audio.source.localData` attribute in the `audio_table` table, which performs the copy operation. It also sets the properties and updates the time stamp for the new BLOB stored in the ORDAudio object. This script is shown in Example 9-7.

Note: This script must be run from the same user as the scripts inExample 9-4, Example 9-5, and Example 9-6.

Example 9-7 copy_audblob.sql

    --copy_audblob.sql
    --

    SET SERVEROUTPUT ON;
    SET ECHO ON;

    -- Use the SQL UPDATE statement to set the contents of
    -- T.audio.source.localData to be the same as the BLOB stored
    -- in the sound column of the soundtable table. This is an easy way
    -- to copy a BLOB stored in the database into a row containing
    -- a column defined as an interMedia ORDAudio object type.
    --
    -- In this case, the BLOB (an audio clip), which was stored in
    -- a row in the soundtable table containing a sound column
    -- defined as a BLOB data type for an ID=1 is copied to a row
    -- in the audio_table table containing an audio column defined as
    -- an ORDAudio object type in which the ID=2. The audio
    -- clip is referenced through the source attribute of the
    -- ORDAudio object type to the underlying localData attribute
    -- of the ORDSource object type.
    --
    -- Then (1) Call setProperties() and (2) call setUpdateTime()
    -- for this new BLOB stored in the ORDAudio object type.
    -- Create a procedure to do this.

    CREATE OR REPLACE PROCEDURE update_proc IS

      obj ORDAudio;
      ctx RAW(64) :=NULL;

    BEGIN
      UPDATE audio_table T SET T.audio.source.localData = (SELECT sound FROM
             soundtable S WHERE S.id = 1) WHERE T.id=2;
      COMMIT;

      SELECT audio INTO obj FROM audio_table WHERE id = 2 FOR UPDATE;
      obj.setProperties(ctx);
      obj.setUpdateTime(SYSDATE);
      UPDATE audio_table SET audio = obj WHERE id = 2;
      COMMIT;

    EXCEPTION
      WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Operation failed');

    END;
    /

    EXECUTE UPDATE_PROC;

#### 9.1.2.6 Show the Properties of the Loaded Audio Data

The `showprop_aud.sql` script displays the properties of the audio data clips stored in the `audio_table` table in rows with `id=1` and `id=2`. They should be identical. Different load methods were used to load the same audio clip from the `soundtable` table into two rows in the `audio_table` table. This script verifies that audio data loaded using the ORDAudio import( ) method matches audio data copied from a BLOB column of the `soundtable` table. This script is shown in Example 9-8.

Note: This script must be run from the same user as the scripts inExample 9-4, Example 9-5, Example 9-6, and Example 9-7.

Example 9-8 showprop_aud.sql

    -- showprop_aud.sql
    --

    SET SERVEROUTPUT ON;
    SET ECHO ON;

    --Query audio_table for ORDAudio content.
    --
    -- This script verifies that the properties of the audio data
    -- in the row with ID=1 (which was populated with the ORDAudio
    -- import() method), match the properties of the audio data
    -- in the row with ID-2 (which was populated by copying data
    -- from a BLOB in the soundtable table.
    --

    DECLARE
      audio ORDAudio;
      idnum integer;
      properties_match BOOLEAN;
      ctx RAW(64) :=NULL;

    BEGIN
    -- Check the properties of the audio data clip imported into the
    -- ORDAudio object type. Properties for ID=1 should be identical
    -- with ID=2.

      dbms_output.put_line(' Properties of these audio clips are identical:');

      SELECT id, audio INTO idnum, audio FROM audio_table WHERE id=1;
      dbms_output.put_line('Properties for id: ' || idnum);

      dbms_output.put_line('audio encoding: ' || audio.getEncoding);
      dbms_output.put_line('audio number of channels: ' ||
                            audio.getNumberOfChannels);
      dbms_output.put_line('audio MIME type: ' || audio.getMimeType);
      dbms_output.put_line('audio file format: ' || audio.getFormat);
      dbms_output.put_line('BLOB Length: ' ||
                            TO_CHAR(audio.getContentLength(ctx)));
      dbms_output.put_line
            ('----------------------------------------------');

    -- Check the properties of the audio data clip copied into the
    -- ORDAudio object type from a BLOB stored in the database.
    -- Properties for ID=1 should be identical with ID=2.

      SELECT id, audio INTO idnum, audio FROM audio_table WHERE id=2;
      dbms_output.put_line('Properties for id: ' || idnum);
      dbms_output.put_line('audio encoding: ' || audio.getEncoding);
      dbms_output.put_line('audio number of channels: ' ||
                            audio.getNumberOfChannels);
      dbms_output.put_line('audio MIME type: ' || audio.getMimeType);
      dbms_output.put_line('audio file format: ' || audio.getFormat);
      dbms_output.put_line('BLOB Length: ' ||
                            TO_CHAR(audio.getContentLength(ctx)));
      dbms_output.put_line
            ('----------------------------------------------');

    END;
    /

The results from running the script `showprop_aud.sql` show that the properties are identical for each stored audio clip.

    Properties of these audio clips are identical:

          Properties for  id:           1

    audio encoding:        MS_PCM
    audio number of channels:  1
    audio MIME type:       audio/x-wav
    audio file format:     WAVE
    BLOB Length:           15932
    ----------------------------------------------
          Properties for id:           2

    audio encoding:        MS_PCM
    audio number of channels:  1
    audio MIME type:       audio/x-wav
    audio file format:     WAVE
    BLOB Length:           15932
    ----------------------------------------------

    PL/SQL procedure successfully completed.

#### 9.1.2.7 Automate the ORDAudio Examples

The `setup_audsample.sql` script runs each of the previous audio scripts in the correct order to automate this process. This script is shown in Example 9-9.

Note: This script must be run from a user with CREATE ANY DIRECTORY and CREATE TABLE privileges.

Example 9-9 setup_audsample.sql

    -- setup_audsample.sql
    --
    -- This script automates the demo by invoking each script in
    -- the required order.
    --

    -- Create the mediadir load directory
    @create_mediadir.sql

    -- Create a soundtable table and populate it with
    -- an audio clip:
    @create_soundtable.sql

    -- Create an audtable table
    @create_audtable.sql

    --import an audio clip
    @import_aud.sql

    -- Copy a BLOB into an ORDAudio object, set the properties,
    -- and update the time:
    @copy_audblob.sql

    -- Check the properties of the audio clips. The properties
    -- should be identical:
    @showprop_aud.sql

    --exit;

#### 9.1.2.8 Clean Up the ORDAudio Examples

The `cleanup_audsample.sql` script removes the sample tables, directories, and procedures created by the previous audio scripts from your database. This script is shown in Example 9-10.

Note: This script must be run from a user with DROP ANY DIRECTORY privilege. This script should be run from the user that was used to run the previous audio scripts.

Example 9-10 cleanup_audsample.sql

    -- cleanup_audsample.sql
    --
    -- This script removes all tables, procedures, and directories
    -- created by this demonstration.  You must have the DROP ANY
    -- DIRECTORY privilege to drop the audio load directory.  This
    -- script should be run under the same user as the demo was run
    -- under.

    -- drop the audio load directory.
    DROP DIRECTORY mediadir;

    -- Drop the tables created by the demo.
    DROP TABLE soundtable PURGE;
    DROP TABLE audio_table PURGE;

    -- Drop the Update procedure.
    DROP PROCEDURE update_proc;

    commit;

## 9.2 Media Data Examples

Media data examples using interMedia include the following common operation on heterogeneous data:

- Using a set of scripts for populating an ORDDoc object from a file data source (See Section 9.2.1)

Reference information on the methods used in these examples is presented in Oracle interMedia Reference.

### 9.2.1 Scripts for Populating an ORDDoc Object from a File Data Source

The scripts presented in this section demonstrate how to populate an ORDDoc object from an existing file.

Table 9-2 lists each script by name, along with a brief description of the operations it performs. Each script is included and described in further detail in the sections that follow.

Table 9-2 Media Scripts

Script Name | Operations Performed
--- | ---
`create_mediadir.sql` Example 9-11 | Creates a media data load directory. (See Section 9.2.1.1)
`create_doctable.sql` Example 9-12 | Creates and populates the `doc_table` table. (See Section 9.2.1.2)
`import_doc.sql` Example 9-13 | Loads the media data. This script imports the media data from a file into the `doc_table` table using the ORDDoc import( ) method. (See Section 9.2.1.3)
`read_doc.sql` Example 9-14 | Reads the media data from a BLOB using a stored procedure. (See Section 9.2.1.4)
`showprop_doc.sql` Example 9-15 | Displays the properties of the loaded media data stored in the `doc_table` table. (See Section 9.2.1.5)
`setup_docsample.sql` Example 9-16 | Automates the process by running the previous media scripts in the required order. (See Section 9.2.1.6)
`cleanup_docsample.sql` Example 9-17 | Cleans up the process by removing the sample tables, directories, and procedures from your database. (See Section 9.2.1.7)

#### 9.2.1.1 Create a Media Data Load Directory

The `create_mediadir.sql` script creates the media data load directory. This script is shown in Example 9-11.

To successfully load the media data, you must have a `mediadir` directory created on your system. This directory must contain your sample audio media files, `aud1.wav` and `aud2.mp3`, which are installed in the `<ORACLE_HOME>``/ord/aud/demo` directory. Actually, you can copy any supported media files to the `mediadir` directory to run this script. Be sure to change the data file name in the script to correspond with the name of the data file you use. This directory path and disk drive must be specified in the CREATE DIRECTORY statement in the `create_mediadir.sql` file.

Note: You must have the CREATE ANY DIRECTORY privilege to run this script. If you wish to delete previous instances of the media data load directory, you will also need the DROP ANY DIRECTORY privilege. You must edit the `create_mediadir.sql` file to replace the directory path in the CREATE DIRECTORY statement. If you run the `create_mediadir.sql` script under a different user than you run the other demo scripts, you must also replace the user in the GRANT READ statement. See the following script for detailed instructions.

Example 9-11 create_mediadir.sql

    -- create_mediadir.sql
    -- You must have the CREATE ANY DIRECTORY privilege to run this script.
    --
    -- You must edit this script to:
    --  o replace the directory path in the CREATE DIRECTORY statement
    --    with your own (see below for details)
    --  o uncomment the GRANT READ ON DIRECTORY statement and grant to the
    --    user under which you will run this demo

    SET SERVEROUTPUT ON;
    SET ECHO ON;

    -- You need DROP ANY DIRECTORY privilege to delete a directory. If there
    -- is no need to delete the directory, then leave the next line
    -- commented out.
    -- DROP DIRECTORY mediadir;

    -- Create the mediadir load directory, the directory where the media
    -- data resides. You must specify the disk drive and path in the
    -- CREATE DIRECTORY statement below. Edit this file to replace the
    -- directory specification with your own.
    CREATE OR REPLACE DIRECTORY mediadir AS 'C:/mediadir';

    -- If you intend to run this demo under a user other than the user
    -- that just created this directory, edit the following grant to grant
    -- READ on the directory just created to the user under which you will
    -- run the demo scripts.  For example, if you will run the demo scripts
    -- under user 'SCOTT', then replace the string "<USER>" with "SCOTT".
    -- Then uncomment the following GRANT statement.  There is no need to
    -- do this if the user creating the directory will also be used to run
    -- the other demo scripts.
    -- GRANT READ ON DIRECTORY mediadir TO <USER>;

#### 9.2.1.2 Create and Populate the doc_table Table

The `create_doctable.sql` script creates and populates the `doc_table` table. This table contains an ORDDoc column. This script creates the `doc_table` table with two columns (`id`, `document`), inserts two rows, and initializes the `document` column. This script is shown in Example 9-12.

Note: Calling the ORDDoc.init( ) method initializes the blob attribute of the ORDDoc object. This is necessary to populate the BLOB with data. This script must be run from a user with the CREATE TABLE privilege.

Example 9-12 create_doctable.sql

    -- create_doctable.sql
    --
    -- This script must be run from a user with the CREATE TABLE privilege.
    --
    -- Create the doc_table table.
    -- Insert two rows with empty ORDDoc columns.
    --

    SET SERVEROUTPUT ON;
    SET ECHO ON;

    DROP TABLE doc_table PURGE;
    CREATE TABLE doc_table (id NUMBER,
                           document ORDDoc)
    LOB(document.source.localData) --store media with 32k chunk
    store as (chunk 32K);

    -- Insert a row with an empty ORDDoc object.
    INSERT INTO doc_table VALUES(1,ORDDoc.init());
    -- Insert a row with an empty BLOB.
    INSERT INTO doc_table VALUES(2,ORDDoc.init());

    COMMIT;

#### 9.2.1.3 Load the Media Data

The `import_doc.sql` script imports media data from a media file into the ORDDoc column in the `doc_table` table using the ORDDoc import( ) method. To import data into a blob attribute where the media data will be stored, the `document` column in the row must be selected `for update`. This script is shown in Example 9-13.

To successfully run this script, you must copy two media files to your `mediadir` directory using the names specified in this script, or modify this script to match the file names of your media files.

Note: This script should be run from the same user as the script in Example 9-12.

Example 9-13 import_doc.sql

    -- import_doc.sql
    --
    -- This script uses the import method to load the media data into the
    -- ORDDoc column.  It then extracts properties from the media using
    -- the setProperties method.
    --
    -- To successfully run this script, you must copy two media files to your
    -- MEDIADIR directory using the names specified in this script, or modify
    -- this script to match the file names of your media.
    --

    SET SERVEROUTPUT ON;
    SET ECHO ON;

    DECLARE
      obj ORDDOC;
      ctx RAW(64) := NULL;

    BEGIN
    -- This imports the audio file aud1.wav from the MEDIADIR directory
    -- on a local file system (srcType=file) and sets the properties.

      SELECT document INTO obj FROM doc_table WHERE id = 1 FOR UPDATE;
      obj.setSource('FILE','MEDIADIR','aud1.wav');
      obj.import(ctx,TRUE);

      UPDATE doc_table SET document = obj WHERE id = 1;
      COMMIT;

    -- This imports the audio file aud2.mp3 from the MEDIADIR directory
    -- on a local file system (srcType=file) and sets the properties.
      SELECT document INTO obj FROM doc_table WHERE id = 2 FOR UPDATE;
      obj.setSource('FILE','MEDIADIR','aud2.mp3');
      obj.import(ctx,TRUE);

      UPDATE doc_table SET document = obj WHERE id = 2;
      COMMIT;
    END;
    /

#### 9.2.1.4 Read the Media Data from the BLOB

The `read_doc.sql` script reads media data from a BLOB by creating the stored procedure `readdocument`. This procedure reads a specified amount of media data from the BLOB attribute, beginning at a particular offset, until all the media data is read. This script is shown in Example 9-14.

Note: This script must be run from the same user as the scripts in Example 9-12 and Example 9-13.

Example 9-14 read_doc.sql

    --read_doc.sql

    SET SERVEROUTPUT ON
    SET ECHO ON

    ----Read from the OrdDoc object column in the doc_table.
    create or replace procedure readdocument as
    obj ORDDoc;
    buffer RAW (32767);
    numBytes BINARY_INTEGER := 32767;
    startpos integer := 1;
    read_cnt integer := 1;
    ctx RAW(64) := NULL;
    BEGIN
      select document into obj from doc_table where id = 1;
      LOOP
        obj.readFromSource(ctx,startPos,numBytes,buffer);
        DBMS_OUTPUT.PUT_LINE('BLOB Length: ' || TO_CHAR(obj.getContentLength()));
        DBMS_OUTPUT.PUT_LINE('start position: '|| startPos);
        DBMS_OUTPUT.PUT_LINE('doing read: ' || read_cnt);
        startpos := startpos + numBytes;
        read_cnt := read_cnt + 1;
       END LOOP;

       -- Note: Add your own code here to process the media data being read;
       -- this routine just reads the data into the buffer 32767 bytes
       -- at a time, then reads the next chunk, overwriting the first
       -- buffer full of data.
       EXCEPTION
         WHEN NO_DATA_FOUND THEN
           DBMS_OUTPUT.PUT_LINE('End of data ');
         WHEN ORDSYS.ORDSourceExceptions.METHOD_NOT_SUPPORTED THEN
           DBMS_OUTPUT.PUT_LINE('ORDSourceExceptions.METHOD_NOT_SUPPORTED caught');
         WHEN OTHERS THEN
           DBMS_OUTPUT.PUT_LINE('EXCEPTION caught');
    END;
    /
    show errors

To execute the stored procedure, enter the following SQL statements:

    SQL> set serveroutput on;
    SQL> execute readdocument
    Content Length: 93594
    start position: 1
    doing read: 1
    start position: 32768
    doing read: 2
    start position: 65535
    doing read: 3
    ----------------
    End of data

    PL/SQL procedure successfully completed.

#### 9.2.1.5 Show the Properties of the Loaded Media Data

The `showprop_doc.sql` script displays the properties of the media data loaded into the `doc_table` table. This script is shown in Example 9-15.

Note: This script must be run from the same user as the scripts in Example 9-12, Example 9-13, and Example 9-14.

Example 9-15 showprop_doc.sql

    -- showprop_doc.sql
    --

    SET SERVEROUTPUT ON;
    SET ECHO ON;

    --
    -- Query doctable for ORDDoc.

    DECLARE
      document ORDDoc;
      idnum integer;
      ctx RAW(64) := NULL;
    BEGIN
      FOR I IN 1..2 LOOP
      SELECT id, document INTO idnum, document FROM doc_table WHERE id=I;
      dbms_output.put_line('document id: '|| idnum);
      dbms_output.put_line('document MIME type: '|| document.getMimeType());
      dbms_output.put_line('document file format: '|| document.getFormat());
      dbms_output.put_line('BLOB Length: '|| TO_CHAR(document.getContentLength()));
      dbms_output.put_line('----------------------------------------------');
      END loop;
    END;
    /

The results from running the script `showprop_doc.sql` are the following:

    SQL> @showprop_doc.sql
    document id:          1
    document MIME type:       audio/xwav
    document file format:     WAVE
    BLOB Length:           93594
    ----------------------------------------------
    document id:          2
    document MIME type:       audio/mpeg
    document file format:     MPGA
    BLOB Length:           51537
    ----------------------------------------------
    PL/SQL procedure successfully completed.

#### 9.2.1.6 Automate the ORDDoc Examples

The `setup_docsample.sql` script runs each of the previous media scripts in the correct order to automate this process. This script is shown in Example 9-16.

Note: This script must be run from a user with CREATE ANY DIRECTORY and CREATE TABLE privileges.

Example 9-16 setup_docsample.sql

    -- setup_docsample.sql
    --
    -- This script automates the demo by invoking each script in
    -- the required order.
    --

    -- Create the mediadir load directory
    @create_mediadir.sql

    -- Create the media table:
    @create_doctable.sql

    --Import 2 media clips and set properties:
    @import_doc.sql

    --Display the properties of the media clips:
    @showprop_doc.sql

    --create stored procedure to read from ordDoc
    @read_doc.sql

    --Execute stored procedure
    execute readdocument

    --exit;

#### 9.2.1.7 Clean Up the ORDDoc Examples

The `cleanup_docsample.sql` script removes the sample tables, directories, and procedures created by the previous media scripts from your database. This script is shown in Example 9-17.

Note: This script must be run from a user with DROP ANY DIRECTORY privilege. This script should be run from the user that was used to run the previous media scripts.

Example 9-17 cleanup_docsample.sql

    -- cleanup_docsample.sql
    --
    -- This script removes all tables and directories created by this
    -- demonstration.  You must have the DROP ANY DIRECTORY privilege
    -- to drop the doc load directory.  This script should be run under
    -- the same user as the demo was run under.

    -- drop the doc load directory.
    DROP DIRECTORY mediadir;

    -- Drop the table created by the demo.
    DROP TABLE doc_table PURGE;

## 9.3 Image Data Examples

Image data examples using interMedia include the following common operations:

- Using a set of scripts for populating an ORDImage object from a file data source (See Section 9.3.1)

- Using a set of scripts for loading an image table from an HTTP data source (See Section 9.3.2)

- Addressing globalization support issues

Reference information on the methods used in these examples is presented in Oracle interMedia Reference.

### 9.3.1 Scripts for Populating an ORDImage Object from a File Data Source

The scripts presented in this section demonstrate how to populate an interMedia ORDImage object from an existing file.

Table 9-3 lists each script by name, along with a brief description of the operations it performs. Each script is included and described in further detail in the sections that follow.

Table 9-3 Image Scripts

Script Name | Operations Performed
--- | ---
`create_mediadir.sql` Example 9-18 | Creates an image data load directory. (See Section 9.3.1.1)
`create_imgtable.sql` Example 9-19 | Creates and populates the `image_table` table. (See Section 9.3.1.2)
`import_img.sql` Example 9-20 | Loads the image data. This script imports the image data from a file into the `image_table` table using the ORDImage import( ) method. (See Section 9.3.1.3)
`read_image.sql` Example 9-21 | Reads the image data from a BLOB using a stored procedure. (See Section 9.3.1.4)
`showprop_img.sql` Example 9-22 | Displays the properties of the loaded image data stored in the `image_table` table. (See Section 9.3.1.5)
`setup_imgsample.sql` Example 9-23 | Automates the process by running the previous image scripts in the required order. (See Section 9.3.1.6)
`cleanup_imgsample.sql` Example 9-24 | Cleans up the process by removing the sample tables, directories, and procedures from your database. (See Section 9.3.1.7)

#### 9.3.1.1 Create an Image Data Load Directory

The `create_mediadir.sql` script creates the image data load directory. This script is shown in Example 9-18.

To successfully load the image data, you must have a `mediadir` directory created on your system. This directory must contain your sample image media files, `img71.gif` and `img50.gif`, which are installed in the `<ORACLE_HOME>``/ord/img/demo` directory. Actually, you can copy any supported image files to the `mediadir` directory to run this script. Be sure to change the data file name in the script to correspond with the name of the data file you use. This directory path and disk drive must be specified in the CREATE DIRECTORY statement in the `create_mediadir.sql` file.

Note: You must have the CREATE ANY DIRECTORY privilege to run this script. If you wish to delete previous instances of the image data load directory, you will also need the DROP ANY DIRECTORY privilege. You must edit the `create_mediadir.sql` file to replace the directory path in the CREATE DIRECTORY statement. If you run the `create_mediadir.sql` script under a different user than you run the other demo scripts, you must also replace the user in the GRANT READ statement. See the following script for detailed instructions.

Example 9-18 create_mediadir.sql

    -- create_mediadir.sql
    -- You must have the CREATE ANY DIRECTORY privilege to run this script.
    --
    -- You must edit this script to:
    --  o replace the directory path in the CREATE DIRECTORY statement
    --    with your own (see below for details)
    --  o uncomment the GRANT READ ON DIRECTORY statement and grant to the
    --    user under which you will run this demo

    SET SERVEROUTPUT ON;
    SET ECHO ON;

    -- You need DROP ANY DIRECTORY privilege to delete a directory. If there
    -- is no need to delete the directory, then leave the next line
    -- commented out.
    -- DROP DIRECTORY mediadir;

    -- Create the mediadir load directory, the directory where the image
    -- clips reside. You must specify the disk drive and path in the
    -- CREATE DIRECTORY statement below. Edit this file to replace the
    -- directory specification with your own.
    CREATE OR REPLACE DIRECTORY mediadir AS 'C:/mediadir';

    -- If you intend to run this demo under a user other than the user
    -- that just created this directory, edit the following grant to grant
    -- READ on the directory just created to the user under which you will
    -- run the demo scripts.  For example, if you will run the demo scripts
    -- under user 'SCOTT', then replace the string "<USER>" with "SCOTT".
    -- Then uncomment the following GRANT statement.  There is no need to
    -- do this if the user creating the directory will also be used to run
    -- the other demo scripts.
    -- GRANT READ ON DIRECTORY mediadir TO <USER>;

#### 9.3.1.2 Create and Populate the image_table Table

The `create_imgtable.sql` script creates and populates the `image_table` table. This table contains an ORDImage column. This script creates the `image_table` table with two columns (`id`, `image`), inserts two rows, and initializes the `image` column. This script is shown in Example 9-19.

Note: Calling the ORDImage.init( ) method initializes the blob attribute of the ORDImage object. This is necessary to populate the BLOB with data. This script must be run from a user with the CREATE TABLE privilege.

Example 9-19 create_imgtable.sql

    -- create_imgtable.sql
    --
    -- This script must be run from a user with the CREATE TABLE privilege.
    --
    -- Create the image_table table.
    -- Insert two  rows with empty OrdImage columns  and initialize object attributes.

    SET SERVEROUTPUT ON;
    SET ECHO ON;

    DROP TABLE image_table PURGE;
    CREATE TABLE image_table (id NUMBER,
                           image ORDImage)
    LOB(image.source.localData) --store image with 32k chunk
    store as (chunk 32K);

    -- Insert rows with empty OrdImage columns and initialize the object attributes.

    INSERT INTO image_table VALUES(1,ORDImage.init());
    INSERT INTO image_table VALUES(2,ORDImage.init());

    COMMIT;

#### 9.3.1.3 Load the Image Data

The `import_img.sql` script imports image data from an image file into the ORDImage column in the `image_table` table using the ORDImage import( ) method. To import data into a blob attribute where the image will be stored, the `image` column in the row must be selected `for update`. This script is shown in Example 9-20.

To successfully run this script, you must copy two image files to your `mediadir` directory using the file names specified in this script, or modify this script to match the file names of your image files.

Note: This script should be run from the same user as the script in Example 9-19.

Example 9-20 import_img.sql

    -- import_img.sql
    --
    SET SERVEROUTPUT ON
    SET ECHO ON

    -- Import the two files into the database.

    DECLARE
      obj ORDIMAGE;
      ctx RAW(64) := NULL;
    BEGIN
    -- This imports the image file img71.gif from the MEDIADIR directory
    -- on a local file system (srcType=file).
    -- the import method  also sets the object properties by reading the image blob.
      select Image into obj from image_table where id = 1 for update;
      obj.setSource('file','MEDIADIR','img71.gif');
      obj.import(ctx);
      update image_table set image = obj where id = 1;
    commit;

    -- This imports the image file img50.gif from the MEDIADIR directory
    select Image into obj from image_table where id = 2 for update;
    obj.setSource('file','MEDIADIR','img50.gif');
    obj.import(ctx);
    update image_table set image = obj where id = 2;
    commit;
    END;
    /

#### 9.3.1.4 Read the Image Data from the BLOB

The `read_image.sql` script reads image data from a BLOB by creating the stored procedure `readimage`. This procedure reads a specified amount of image data from the BLOB attribute, beginning at a particular offset, until all the image data is read. This script is shown in Example 9-21.

Note: This script must be run from the same user as the scripts in Example 9-19 and Example 9-20.

Example 9-21 read_image.sql

    -- read_image.sql
    set serveroutput on
    set echo on
    create or replace procedure readimage as
    -- Note: ORDImage has no readFromSource method like ORDAudio
    -- and ORDVideo; therefore, you must use the DBMS_LOB package to
    -- read image data from a BLOB.
    buffer RAW (32767);
    src BLOB;
    obj ORDImage;
    amt BINARY_INTEGER := 32767;
    pos integer := 1;
    read_cnt integer := 1;
    BEGIN
      Select t.image.getcontent() into src from image_table t where t.id = 1;
      Select image into obj from image_table t where t.id = 1;
    DBMS_OUTPUT.PUT_LINE('Content length is: '||      TO_CHAR(obj.getContentLength()));
    LOOP
    DBMS_LOB.READ(src,amt,pos,buffer);
    DBMS_OUTPUT.PUT_LINE('start position: '|| pos);
    DBMS_OUTPUT.PUT_LINE('doing read '|| read_cnt);
    pos := pos + amt;
    read_cnt := read_cnt + 1;
    -- Note: Add your own code here to process the image data being read;
    -- this routine just reads data into the buffer 32767 bytes
    -- at a time, then reads the next chunk, overwriting the first
    -- buffer full of data.
    END LOOP;
    EXCEPTION
    WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('----------------');
    DBMS_OUTPUT.PUT_LINE('End of data ');
    END;
    /
    show errors

To execute the stored procedure, enter the following SQL statements:

    SQL> set serveroutput on;
    SQL> execute read_image;
    Content length is: 1124
    start position: 1
    doing read  1
    ----------------
    End of data

    PL/SQL procedure successfully completed.

#### 9.3.1.5 Show the Properties of the Loaded Image Data

The `showprop_img.sql` script displays the properties of the image data loaded into the `image_table` table. This script is shown in Example 9-22.

Note: This script must be run from the same user as the scripts in Example 9-19, Example 9-20, and Example 9-21.

Example 9-22 showprop_img.sql

    -- showprop_img.sql
    --

    SET SERVEROUTPUT ON;
    SET ECHO ON;

    -- This script displays properties of the image stored in image_table
    --

    DECLARE
     image ORDImage;
     idnum integer;
     rowcount integer;
    BEGIN

      FOR I IN 1..2 LOOP
        SELECT id, image into idnum, image from image_table where id=I;
        dbms_output.put_line('Image properties:');
        dbms_output.put_line('image id: '|| idnum);
        dbms_output.put_line('image height: '|| image.getHeight());
        dbms_output.put_line('image width: '|| image.getWidth());
        dbms_output.put_line('image MIME type: '|| image.getMimeType());
        dbms_output.put_line('image file format: '|| image.getFileFormat());
        dbms_output.put_line('BLOB Length: '|| TO_CHAR(image.getContentLength()));
        dbms_output.put_line('-------------------------------------------');
      END loop;
    END;
    /

The results from running the script `showprop_img.sql` are the following:

    SQL> @showprop_img.sql
    image id:          1

    image height:      15
    image width:       43
    image MIME type:   image/gif
    image file format: GIFF
    BLOB Length:       1124
    -------------------------------------------
    image id:          2

    image height:      32
    image width:       110
    image MIME type:   image/gif
    image file format: GIFF
    BLOB Length:       686
    -------------------------------------------

    PL/SQL procedure successfully completed.

#### 9.3.1.6 Automate the ORDImage Examples

The `setup_imgsample.sql` script runs each of the previous image scripts in the correct order to automate this process. This script is shown in Example 9-23.

Note: This script must be run from a user with CREATE ANY DIRECTORY and CREATE TABLE privileges.

Example 9-23 setup_imgsample.sql

    -- setup_imgsample.sql
    --
    -- This script automates the demo by invoking each script in
    -- the required order.
    --

    -- Create the imgdir load directory
    @create_mediadir.sql

    -- Create image table:
    @create_imgtable.sql

    --Import images into image_table
    @import_img.sql

    --Show properties of images
    @showprop_img.sql

    --create stored procedure to read from ordImage
    @read_image.sql

    --Execute stored procedure
    execute readimage

    --exit;

#### 9.3.1.7 Clean Up the ORDImage Examples

The `cleanup_imgsample.sql` script removes the sample tables, directories, and procedures created by the previous image scripts from your database. This script is shown in Example 9-24.

Note: This script must be run from a user with DROP ANY DIRECTORY privilege. This script should be run from the user that was used to run the previous image scripts.

Example 9-24 cleanup_imgsample.sql

    -- cleanup_imgsample.sql
    --
    -- This script removes all tables, procedures, and directories
    -- created by this demonstration.  You must have the DROP ANY
    -- DIRECTORY privilege to drop the image load directory.  This
    -- script should be run under the same user as the demo was run
    -- under.

    -- drop the image load directory.
    DROP DIRECTORY mediadir;

    -- Drop the tables created by the demo.
    DROP TABLE image_table PURGE;

    -- Drop the Update procedure.
    DROP PROCEDURE read_image;

    commit;
    exit;

### 9.3.2 Script for Loading an Image Table from an HTTP Data Source

The `import_imghttp.sql` script imports the image data from an HTTP data source . This script inserts two rows into the `image_table` table and loads the image data from the specified HTTP data source (source type `HTTP`, URL location, and HTTP object name). This script is shown in Example 9-25.

To successfully run this script, you must modify it to point to two images located on your Web site.

Example 9-25 import_imghttp.sql Script

    --import_imghttp.sql
    -- Import the two HTTP images from a Web site into the database.
    -- PreRequisites:
    --    You will need to do the following  before running this script
    --        1. Run create_imgdir.sql
    --        2. Run create_imgtable.sql
    --        3. Modify the HTTP URL and object name to point to two images on
    --           your own Web site.

    SET SERVEROUTPUT ON
    SET ECHO ON

    -- Import two images from HTTP source URLs.

    -- Insert two rows with an empty BLOB.
    -- See above section on pre requisites
    insert into image_table values (7,ORDImage.init(
    'http','http://your_website/images','image1.jpg'));

    insert into image_table values (8,ORDImage.init(
    'http','http://your_website/images','image2.gif'));

    commit;

    DECLARE
      obj ORDIMAGE;
      ctx RAW(64) := NULL;
    BEGIN
    -- This imports the image file image1.gif from the HTTP source URL
    -- (srcType=HTTP), and automatically sets the properties.
      select Image into obj from image_table where id = 7 for update;
      obj.import(ctx);
      update image_table set image = obj where id = 7;
      commit;
    -- This imports the image file image2.gif from the HTTP source URL
    -- (srcType=HTTP), and automatically sets the properties.
      select Image into obj from image_table where id = 8 for update;
      obj.import(ctx);
      update image_table set image = obj where id = 8;
      commit;
    END;
    /

### 9.3.3 Addressing Globalization Support Issues

Example 9-26 shows how to use the processCopy( ) method with language settings that use the comma as the decimal point. For example, when the territory is `FRANCE`, the decimal point is expected to be a comma. Notice that the `",75"` is specified as the scale factor. This example addresses globalization support issues.

Example 9-26 Address a Globalization Support Issue

    ALTER SESSION SET NLS_LANGUAGE = FRENCH;
    ALTER SESSION SET NLS_TERRITORY = FRANCE;
    DECLARE
      myimage ORDImage;

    BEGIN

      SELECT image into myimage from image_table where id=1 for update;

      myimage.ProcessCopy('scale=",75"', myimage);
      myimage.setProperties();
      UPDATE image_table SET image = myimage where id=1;
      COMMIT;
    END;
    /

Run the `showprop_img.sql` script to see the properties of the scaled image.

## 9.4 Video Data Examples

See Oracle interMedia Reference for video data examples.